﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
/// <summary>
/// Dataaccess class for Travel
/// </summary>
namespace DBLayer
{
    public class TravelDB
    {
        //Variabler initialization
        private SqlConnection con;
        private SqlUtility sqlUtility;
        private PersonDB persondb;

        public TravelDB()
        {
            sqlUtility = new SqlUtility();
            persondb = new PersonDB();
        }
        //Count rows in table in database
        private int CountDatabase()
        {
            int count;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT COUNT (TravelId) from Travel";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    count = (int)cmd.ExecuteScalar();
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return count;

        }
        //Insert Travel into database
        public int InsertTravel(Travel _travel)
        {
            int count = CountDatabase() + 1;
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "INSERT INTO Travel (travelId, totalPrice, startDestination, endDestination) VALUES (@travelId, @totalPrice, @startDestination, @endDestination)";

            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@travelId", count);
            cmd.Parameters.AddWithValue("@totalPrice", _travel.TotalPrice);
            cmd.Parameters.AddWithValue("@startDestination", _travel.StartDestination.AirportCode);
            cmd.Parameters.AddWithValue("@endDestination", _travel.EndDestination.AirportCode);

            sqlUtility.ExecuteData(cmd, connect);
            _travel.TravelId = count;
            if (_travel.Persons.Count != 0 && _travel.SeatBooks.Count != 0)
            {
                
                foreach(Customer cs in _travel.Persons)
                {
                    InsertTravelPerson(_travel, cs);
                }
                SeatBookingDB seatBookingDB = new SeatBookingDB();
                foreach(SeatBooking sb in _travel.SeatBooks)
                {
                    seatBookingDB.InsertSeatBooking(sb, _travel.TravelId);
                }
            }
              return 0;
        }
        //Insert person into database

        //find Travel by  int (travelID) 
        public int InsertTravelPerson(Travel _travel, Customer _customer)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "INSERT INTO TravelPerson (phoneNo, travelId) VALUES (@phoneNo, @travelId)";

            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@phoneNo", _customer.PhoneNo);
            cmd.Parameters.AddWithValue("@travelId", _travel.TravelId);

            return sqlUtility.ExecuteData(cmd, connect);

        }
        public Travel FindTravel(int _travelId)
        {
            Travel tempTravel = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "SELECT * from Travel WHERE travelId = @travelId";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@travelId", _travelId);
                    using (SqlDataReader _reader = cmd.ExecuteReader())
                    {
                        if (_reader.Read())
                        {
                            tempTravel = BuildTravel(_reader);
                        }
                        _reader.Close();
                        _reader.Dispose();
                    }
                    cmd.Dispose();
                }

                con.Close();
                con.Dispose();
            }
            return tempTravel;
        }
        //Update Travel by obj
        public int UpdateTravel(Travel _travel)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "UPDATE travet set travelId = @travelId, totalPrice = @totalPrice, startDestination = @startDestination, endDistination = @endDistination WHERE travelId = @travelId";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@travelId", _travel.TravelId);
            cmd.Parameters.AddWithValue("@totalPrice", _travel.TotalPrice);
            cmd.Parameters.AddWithValue("@startDestination", _travel.StartDestination.AirportCode);
            cmd.Parameters.AddWithValue("@endDistination", _travel.EndDestination.AirportCode);

            return sqlUtility.ExecuteData(cmd, connect);

        }
        //build object Travel from data from database
        private Travel BuildTravel(SqlDataReader _reader)
        {
            PersonDB personDB = new PersonDB();
            SeatBookingDB seatBooking = new SeatBookingDB();
            Travel temTravel = new Travel();

            temTravel.TravelId = (int) _reader["TravelId"];
            decimal temPrice = (decimal) _reader["totalPrice"];
            temTravel.TotalPrice  = System.Convert.ToDouble(temPrice);
            temTravel.StartDestination = new Airport();
            temTravel.StartDestination.AirportCode = (string) _reader["startDestination"];
            temTravel.EndDestination = new Airport();
            temTravel.EndDestination.AirportCode = (string) _reader["endDestination"];

            return temTravel;
        }
        //return a List<Travel> with all the Travel in the database
        public List<Travel> SelectAll()
        {
            List<Travel> travels = new List<Travel>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Travel";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader _reader = cmd.ExecuteReader())
                    {
                        while (_reader.Read())
                        {
                            Travel temTravel = new Travel();
                            temTravel = BuildTravel(_reader);
                            travels.Add(temTravel);
                        }
                        _reader.Close();
                        _reader.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return travels;
        }
        //remove Travel from database
        public int RemoveTravel(Travel _travel)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from Travel WHERE travelId = @travelId";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@travelId", _travel.TravelId);

            return sqlUtility.ExecuteData(cmd, connect);
        }
    }
}
